home *** CD-ROM | disk | FTP | other *** search
/ PC World 2008 February (DVD) / PCWorld_2008-02_DVD.iso / v cisle / PHP / PHP.exe / EasyPHP-2.0b1-setup.exe / {app} / sqlitemanager / include / sql.class.php < prev    next >
Encoding:
PHP Script  |  2006-04-18  |  12.6 KB  |  430 lines

  1. <?php
  2. /**
  3. * Web based SQLite management
  4. * @package SQLiteManager
  5. * @author FrΘdΘric HENNINOT
  6. * @version $Id: sql.class.php,v 1.62 2006/04/14 15:16:52 freddy78 Exp $ $Revision: 1.62 $
  7. */
  8.  
  9. class sql {
  10.  
  11.     /**
  12.     * @access public
  13.     * @var objet
  14.     * Class instance of database connection
  15.     */
  16.     var $connId;
  17.  
  18.     /**
  19.     * @access private
  20.     * @var string
  21.     * current query
  22.     */
  23.     var $query;
  24.  
  25.     /**
  26.     * @access private
  27.     * @var bool
  28.     * if true, all query are journalised into file
  29.     */
  30.     var $journalised;
  31.  
  32.     /**
  33.     * @access private
  34.     * @var string
  35.     * filename where query are store when journalised
  36.     */
  37.     var $journalFile;
  38.  
  39.     /**
  40.     * @access public
  41.     * @var bool
  42.     * true if query return data
  43.     */
  44.     var $withReturn;
  45.  
  46.     /**
  47.     * @access public
  48.     * @var bool
  49.     * true if the current query content are multiple
  50.     */
  51.     var $multipleQuery;
  52.  
  53.     /**
  54.     * Last query Execution Time (msec)
  55.     *
  56.     * @access public
  57.     * @var integer
  58.     */
  59.     var $queryTime;
  60.  
  61.     /**
  62.     * @access private
  63.     * @var integer
  64.     * Number lines has been changes by the current query
  65.     */
  66.     var $changesLine;
  67.  
  68.     /**
  69.     * @access private
  70.     * @var integer
  71.     * Number of query into the curent multiple query
  72.     */
  73.     var $nbQuery;
  74.  
  75.     /**
  76.     * @access private
  77.     * @var bool
  78.     * true if has an error in the current query
  79.     */
  80.     var $error;
  81.  
  82.     /**
  83.     * @access private
  84.     * @var integer
  85.     * Error line number for multiple query
  86.     */
  87.     var $lineError;
  88.  
  89.     /**
  90.     * @access private
  91.     * @var string
  92.     * SQLite error message
  93.     */
  94.     var $errorMessage;
  95.  
  96.     /**
  97.     * @access private
  98.     * @var string
  99.     * SQLite query executed when error
  100.     */
  101.     var $errorQuery;
  102.  
  103.     /**
  104.     * Class constructor
  105.     *
  106.     * @access public
  107.     * @param object $handle instance of connection class
  108.     * @param string $query
  109.     * @param string $journal filename of the journalise file
  110.     */
  111.     function sql(&$handle, $query, $journal=''){
  112.         $this->connId = $handle;
  113.         $this->query = trim($query);
  114.         if(eregi('^(select|explain|pragma)[[:space:]]', $this->query)) $this->withReturn = true;
  115.         else $this->withReturn = false;
  116.         if($journal != ''){
  117.             $this->journalised = true;
  118.             $this->journalFile = $journal;
  119.         } else $this->journalised = false;
  120.         return;
  121.     }
  122.  
  123.     /**
  124.     * Verify and exec query
  125.     *
  126.     * @access public
  127.     */
  128.     function verify($autocommit=true){
  129.         if(is_resource($this->connId->connId) || is_object($this->connId->connId)){
  130.             if(!empty($GLOBALS['attachDbList'])){
  131.                 // attachment of all database
  132.                 foreach($GLOBALS['attachDbList'] as $attachDbId) {
  133.                     $attachQuery = 'ATTACH DATABASE '.quotes($GLOBALS['attachInfo'][$attachDbId]['location']).' AS '.quotes($GLOBALS['attachInfo'][$attachDbId]['name']).';';
  134.                     $this->execQuery($attachQuery);
  135.                 }
  136.             }
  137.             if($this->query != ''){
  138.                 $parsing =& new ParsingQuery($this->query, ((isset($_POST['sqltype']))? $_POST['sqltype'] : 1 ));
  139.                 $tabQuery = $parsing -> convertQuery();
  140.                 if(!is_array($tabQuery)){
  141.                     $this->multipleQuery = false;
  142.                     $this->query = $tabQuery;
  143.                     if(!$result = $this->execQuery($tabQuery)) {    
  144.                         $this->addChanges();
  145.                     }
  146.                     return $result;
  147.                 } else {
  148.                     $time=0;
  149.                     $this->multipleQuery = true;
  150.                     $this->connId->connId->query('BEGIN TRANSACTION;');
  151.                     $error = false;
  152.                     $lineNum = 1;
  153.                     $this->changesLine = $queryNum = 0;
  154.                     foreach($tabQuery as $query){
  155.                         if(!empty($query) && substr(trim($query), 0, 1)!='#'){
  156.                             if($this->_checkBeginQuery($query)){
  157.                                 $queryNum++;
  158.                                 if(isset($commitafter) && $commitafter){
  159.                                     $this->connId->connId->query('COMMIT TRANSACTION;');
  160.                                     $this->connId->connId->query('BEGIN TRANSACTION;');
  161.                                     $commitafter=false;
  162.                                 }
  163.                             }
  164.                             if($this->_checkBeginQuery($query, 'CREATE|DROP') && !eregi('^create[[:space:]]database', $query)) {
  165.                                 if ($autocommit) $commitafter = true;
  166.                             }
  167.                             if($this->execQuery($query)){
  168.                                 $error = true;
  169.                                 $this->lineError[] = $lineNum;
  170.                             }
  171.                             $time += $this->queryTime;
  172.                             $this->addChanges();
  173.                             $lineNum++;
  174.                         }
  175.                         if($error) break;
  176.                     }
  177.                     if($error) {
  178.                         $this->connId->connId->query('ROLLBACK TRANSACTION;');
  179.                     } else {
  180.                         $this->connId->connId->query('COMMIT TRANSACTION;');
  181.                     }
  182.                     $this->error = $error;
  183.                     $this->withReturn = false;
  184.                     $this->nbQuery = $queryNum;
  185.                 }
  186.             } else {
  187.                 $this->error = true;
  188.                 $this->errorMessage = $GLOBALS['traduct']->get(64);
  189.             }
  190.         } else {
  191.             $this->error = true;
  192.             $this->errorMessage = $GLOBALS['traduct']->get(65);
  193.         }
  194.         $this->queryTime = (isset($time)?$time:0);
  195.         return $this->error;
  196.     }
  197.     
  198.     function addChanges() {
  199.         $tempChanges = $this->connId->connId->changes();
  200.         $this->changesLine += $tempChanges;
  201.         return $tempChanges;
  202.     }
  203.  
  204.     /**
  205.     * Exec, manage error and journalised
  206.     *
  207.     * @access public
  208.     * @param string $string query
  209.     */
  210.     function execQuery($query){
  211.         $queryExec = $this->cleanup($query);
  212.         $this->errorQuery = '';
  213.         $this->queryLog($queryExec);
  214.         if(!eregi('^create[[:space:]]database', $queryExec)){
  215.             $GLOBALS['phpSQLiteError'] = '';
  216.             set_error_handler('phpSQLiteErrorHandling');
  217.             if($this->connId->getResId($queryExec)){
  218.                 $this->error = false;
  219.             } else {
  220.                 $this->error = true;
  221.                 $this->errorQuery = $queryExec;
  222.                 $this->errorMessage = '<table style="color: red;"><tr><td>'.$GLOBALS['traduct']->get(9).' :</td><td>'.$this->connId->connId->getError().'</td></tr>';
  223.                 if($GLOBALS['phpSQLiteError'] != '') $this->errorMessage .= '<tr><td> </td><td>'.$GLOBALS['phpSQLiteError'].'</td></tr>';
  224.                 if(strstr($GLOBALS['phpSQLiteError'],'syntax error') && $this->multipleQuery)
  225.                     $this->errorMessage .= '<tr><td valign="top"><pre class="error_query">Query :</pre></td><td><pre class="error_query">'.htmlentities($this->errorQuery, ENT_NOQUOTES, $GLOBALS['charset']).'</pre></td></tr>';
  226.                 $this->errorMessage .= '</table>';
  227.             }
  228.             $this->queryTime = $this->connId->queryTime;
  229.             restore_error_handler();
  230.         } else {
  231.             // emulating 'CREATE DATABASE'
  232.             preg_match('/CREATE[[:space:]]DATABASE(.*)/i', $queryExec, $result);
  233.             $newDatabase = $result[1];
  234.             if(strrpos($newDatabase, ';')) $newDatabase = substr($newDatabase, 0, strrpos($newDatabase, ';'));
  235.             if(eregi('[[:space:]]as[[:space:]]', $newDatabase)){
  236.                 preg_match('/(.*)[[:space:]]AS(.*)/i', $newDatabase, $result);
  237.                 $newDatabaseName = trim($result[1]);
  238.                 $newDatabaseFilename = trim($result[2]);
  239.             } else {
  240.                 $newDatabaseName = $newDatabaseFilename = trim($newDatabase);
  241.             }
  242.             unset($GLOBALS['workDb']);
  243.             include_once INCLUDE_LIB.'SQLiteDbConnect.class.php';
  244.             $tempdir = dirname($newDatabaseFilename);
  245.             if($tempdir == '.') $newDatabaseFilename = DEFAULT_DB_PATH . $newDatabaseFilename;
  246.             $GLOBALS['workDb'] = &new SQLiteDbConnect($newDatabaseFilename);
  247.             $GLOBALS['workDb']->includeUDF();
  248.             $this->connId = $GLOBALS['workDb'];
  249.  
  250.             $query = 'INSERT INTO database (name, location) VALUES ('.quotes($newDatabaseName).', '.quotes(DEFAULT_DB_PATH.$newDatabaseFilename).')';
  251.             if(!$GLOBALS['db']->query($query)) {
  252.                 $error = true;
  253.                 $this->errorQuery = $query;
  254.                 $message .= '<li><span style="color: red; font-size: 11px">'.$GLOBALS['traduct']->get(100).'</span></li>';
  255.             } else {
  256.                 if(DEBUG) $GLOBALS['dbsel'] = $GLOBALS['db']->last_insert_id();
  257.                 else $GLOBALS['dbsel'] = @$GLOBALS['db']->last_insert_id();
  258.                 echo "<script type=\"text/javascript\">parent.left.location='left.php?dbsel=".$GLOBALS['dbsel']."';</script>";
  259.             }
  260.         }
  261.         return $this->error;
  262.     }
  263.  
  264.  
  265.     /**
  266.     * Cleanup POST query
  267.     * and convert MySQL type into SQLite type
  268.     *
  269.     * @access public
  270.     * @param array $data data table
  271.     * @param string $width width of the end table (px or %)
  272.     */
  273.     function cleanup($query){
  274.         $query = stripslashes($query);
  275.         if(!isset($_POST['sqltype'])) $_POST['sqltype']=1;
  276.         if($_POST['sqltype']==2){
  277.             $query = str_replace("\'", "''", $query);
  278.             $query = str_replace("\\\"", "\"\"", $query);
  279.         }
  280.         return $query;
  281.     }
  282.  
  283.     /**
  284.     * Manual query Form
  285.     *
  286.     * @access public
  287.     * @param string $query
  288.     */
  289.     function getForm($query){
  290.         //Mozilla textarea bigger than IE one
  291.         $mozIE = (!strstr($_SERVER["HTTP_USER_AGENT"],'IE'));
  292.       echo '<!-- sql.class.php : getForm() -->'."\n";
  293.         echo '<div align="center">';
  294.         echo '    <form name="sql" action="main.php?dbsel='.$GLOBALS['dbsel'].'&table='.$GLOBALS['table'].'" method="POST" ENCTYPE="multipart/form-data" target="main">
  295.                 <table class="Insert" cellspacing="0" cellpadding="0">
  296.                 <thead>
  297.                     <tr>
  298.                         <td class="Browse" colspan="2">'.$GLOBALS['traduct']->get(66).'</b></td>
  299.                     </tr>
  300.                 </thead>
  301.                 <tr>
  302.                     <td class="DisplayQuery">
  303.                         <div>
  304.                             <textarea wrap="hard" name="DisplayQuery" cols="80" rows="'.(TEXAREA_NB_ROWS-$mozIE).'" onfocus="document.sql.DisplayQuery.select();">'.$this->cleanup($query).'</textarea>
  305.                         </div>
  306.                         <div align="left">'.$GLOBALS['traduct']->get(67).' :
  307.                             <input type="file" size="35" class="file" name="sqlFile">    
  308.                             <input type="hidden" name="action" value="sql">
  309.                         </div>
  310.                     </td>';
  311.         if(!empty($GLOBALS['table'])) {
  312.             echo '<td align="center" style="padding-top: 1px;" valign="top">'.$this->GetColumnSelect().'</td>';
  313.         }
  314.         echo '    </tr>
  315.                 </table>'."\n";
  316.         echo '    <div style="padding:5px;" align="center">'.$GLOBALS['traduct']->get(68).'
  317.                     <input type="radio" name="sqltype" value=1 '.(((!isset($_POST['sqltype'])) || ($_POST['sqltype']==1))? 'checked="checked"' : '' ).'> - MySQL
  318.                     <input type="radio" name="sqltype" value="2"'.(((isset($_POST['sqltype'])) && ($_POST['sqltype']==2))? ' checked="checked"' : '' ).'><br/>
  319.                     <input class="button" type="submit" value="'.$GLOBALS['traduct']->get(69).'">
  320.                 </div>'."\n";
  321.         echo '    </form>'."\n".'</div>';
  322.     if($this->connId->connId->getVersion()!=3) {
  323.         echo '<table class="Tip"><tr><th class="TipTitle">';
  324.         echo $GLOBALS['traduct']->get(224);
  325.         echo '</th></tr><tr><td class="TipBody">';
  326.         echo "SELECT php('sprinf','%02d',MyColumn) FROM TABLE;</br>"."\n";
  327.         echo '</td></tr></table>';
  328.     }
  329.     echo '</body>'."\n";
  330.     echo '</html>';
  331.     }
  332.  
  333.     /**
  334.     * Verify if the result can be modify or deleted
  335.     * if true, return the table name else return false
  336.     *
  337.     * @access public
  338.     * @param string $query
  339.     */
  340.     function checkAccessResult($query){
  341.         if(eregi('EXPLAIN|JOIN|GROUP[[:space:]]', $query)) return false;
  342.         $match = 'WHERE|ORDER|LIMIT';
  343.         if(eregi($match, $query)) preg_match('/FROM(.*)('.$match.')/i', $query, $result);
  344.         else preg_match('/FROM(.*)/i', $query, $result);
  345.         if(isset($result[1])) {
  346.             $listTable = trim($result[1]);
  347.             $posEnd = strrpos($listTable, ';');
  348.             if($posEnd) $listTable = substr($listTable, 0, $posEnd);
  349.         } else $listTable = '';
  350.         $GLOBALS['TableListImpact'] = $listTable;
  351.         if(strpos($listTable, ',')) return false;
  352.         $tableNAme = trim($listTable);
  353.         if($res = $this->connId->getResId('SELECT type FROM sqlite_master WHERE name LIKE '.quotes($tableNAme)));
  354.         if(@$this->connId->connId->fetch_single() != 'table') {
  355.             return false;
  356.         } else return $tableNAme;
  357.     }
  358.  
  359.     /**
  360.     * Log string into journal file
  361.     *
  362.     * @access public
  363.     * @param string $string
  364.     */
  365.     function queryLog($string){
  366.         if($this->journalised){
  367.             $fp = fopen($this->journalFile, 'a+');
  368.             fwrite($fp, $string."\n");
  369.             fclose($fp);
  370.         }
  371.     }
  372.  
  373.     /**
  374.     * Verify if the string param is a start of string
  375.     * and if param motif is set, check if the start query content this
  376.     *
  377.     * @access public
  378.     * @param string $req query
  379.     * @param string $motif
  380.     */
  381.     function _checkBeginQuery($req, $motif=NULL){
  382.         if(preg_match('/^\s*(select|insert|update|delete|create|drop|replace|pragma)\s/i', $req)) {
  383.             if(strlen($motif) != '') {
  384.                 return preg_match('/^\s*('.$motif.')\s/i', $req) == 1;
  385.             }
  386.         return true;
  387.         }
  388.         return false;
  389.     }
  390.  
  391.     /**
  392.     * Display result when the query is multiple
  393.     *
  394.     * @access public
  395.     */
  396.     function DisplayMultipleResult(){
  397.         echo '
  398.             <table width="60%" align="center">
  399.                 <tr><td bgcolor="lightblue" >'.$this->nbQuery.' '.$GLOBALS['traduct']->get(70).' '.$this->queryTime.' '.$GLOBALS['traduct']->get(214).'</td></tr>
  400.                 <tr><td bgcolor="#CCCCCC"><span class="sqlsyntaxe"> '.$this->changesLine.' '.$GLOBALS['traduct']->get(71).'</span></td></tr>
  401.             </table>';
  402.         return;
  403.     }
  404.  
  405.     /**
  406.     * Get column of table select for SQL
  407.     *
  408.     * @access private
  409.     */
  410.     function GetColumnSelect(){
  411.         $query = 'PRAGMA table_info('.brackets($GLOBALS['table']).');';
  412.         $tableInfoTable = array();
  413.         $out = '';
  414.         if($this->connId->getResId($query)){
  415.             $tableInfoTable = $this->connId->getArray();
  416.         }
  417.         if(!empty($tableInfoTable)){
  418.             $optionList = array();
  419.             foreach($tableInfoTable as $columnInfo){
  420.                 $optionList[] = '<option value="'.brackets($GLOBALS['table']).'.'.brackets($columnInfo['name']).'">'.brackets($GLOBALS['table']).'.'.brackets($columnInfo['name']).'</option>';
  421.             }
  422.             $out = '<div><select name="columnTable" size="'.TEXAREA_NB_ROWS.'" multiple="multiple">'."\n".implode("\n", $optionList).'</select></div>'."\n";
  423.             $out .= '<div><input name="insertButton" class="button" type="button" value="'.$GLOBALS['traduct']->get(75).'" onClick="insertColumn();"></div>';
  424.         }
  425.         return $out;
  426.     }
  427. }
  428.  
  429. ?>
  430.